﻿using System;
using System.Data;
using System.Configuration;
using System.Globalization;

namespace BizElements.Core.ProviderComponents
{
    /// <summary>Executes hierarchical queries.</summary>
    sealed class GenericHierarchicalQueryExecutor : IHierarchicalQueryExecutor
    {
        #region Common.

        private static void AppendData(DataTable source, ref DataTable target)
        {
            if (target == null)
                target = source.Clone();

            foreach (DataRow row in source.Rows)
                target.ImportRow(row);
        }

        enum LevelQuerySelectList
        {
            AllColumns,
            Count,
            Default,
            PrimaryKey
        }

        private static SelectStatement CreateSelectFromLevelQuery(IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int level, LevelQuerySelectList itemsToSelect)
        {
            if (level > Settings.GenericHierarchicalQueryExecutorMaxLevel)
                throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Messages.GenericHierarchicalQueryExecutor_MaxLevelReachedFormat, Settings.GenericHierarchicalQueryExecutorMaxLevel));

            // Target level table must be child table because that is the table which is used in leafFilter.
            // If leaf filter is used then the we must ensure that proper table name is used for target table.
            IDbTable targetLevelTable;
            if (leafFilter != null && !leafFilter.IsEmpty)
                targetLevelTable = recursiveRelation.Child;
            else
                targetLevelTable = rootEntity.Table.Clone("L" + level);

            SelectStatement selectFromTargetLevel = new SelectStatement(targetLevelTable);
            CreateSelectListItems(itemsToSelect, targetLevelTable, selectFromTargetLevel);

            if (leafFilter != null && !leafFilter.IsEmpty)
                selectFromTargetLevel.Where.Add(leafFilter);

            IDbTable prevLevel = targetLevelTable;
            for (int parentLevel = level - 1; parentLevel >= 0; parentLevel--)
            {
                IDbTable nextLevel = rootEntity.Table.Clone("L" + parentLevel);
                DbRelation joinLevels = JoinLevelsInSubTree(prevLevel, nextLevel, recursiveRelation);
                selectFromTargetLevel.Relations.Add(joinLevels, false, false);
                prevLevel = nextLevel;
            }

            IDbTable subTreeRoot = prevLevel;
            foreach (IDbColumn rootPkPart in subTreeRoot.PrimaryKey)
                selectFromTargetLevel.Where.Add(rootPkPart, rootEntity.GetField(rootPkPart));

            return selectFromTargetLevel;
        }

        private static void CreateSelectListItems(LevelQuerySelectList itemsToSelect, IDbTable targetLevelTable, SelectStatement selectFromTargetLevel)
        {
            if (itemsToSelect == LevelQuerySelectList.AllColumns)
                selectFromTargetLevel.SelectList.Add(targetLevelTable.Columns);
            else if (itemsToSelect == LevelQuerySelectList.Count)
                selectFromTargetLevel.SelectList.Add(AggregateFunctionFactory.Count("nodeCount"));
            else if (itemsToSelect == LevelQuerySelectList.PrimaryKey)
                selectFromTargetLevel.SelectList.Add(targetLevelTable.PrimaryKey);
        }

        private static DbRelation JoinLevelsInSubTree(IDbTable prevLevel, IDbTable nextLevel, DbRelation recursiveRelation)
        {
            int keyLen = recursiveRelation.ChildForeignKey.Length;
            IDbColumn[] parentPkCols = new IDbColumn[keyLen];
            IDbColumn[] childFkCols = new IDbColumn[keyLen];
            for (int keyPart = 0; keyPart < keyLen; keyPart++)
            {
                string pkPartName = recursiveRelation.ParentPrimaryKey[keyPart].ColumnName;
                parentPkCols[keyPart] = nextLevel.Columns.GetByColumnName(pkPartName);
                string fkPartName = recursiveRelation.ChildForeignKey[keyPart].ColumnName;
                childFkCols[keyPart] = prevLevel.Columns.GetByColumnName(fkPartName);
            }

            DbRelation joinLevels = new DbRelation(nextLevel, parentPkCols, prevLevel, childFkCols, null);
            return joinLevels;
        }

        internal static DataTable SortDataTable(DataTable unsortedData, OrderByClause orderBy)
        {
            bool shouldSort = (orderBy != null && orderBy.Count > 0);
            if (!shouldSort)
                return unsortedData;

            DataRow[] sortedRows = unsortedData.Select(null, orderBy.GetDataViewSortExpression());
            DataTable sortedData = unsortedData.Clone();
            sortedData.BeginLoadData();
            foreach (DataRow row in sortedRows)
                sortedData.ImportRow(row);

            sortedData.EndLoadData();
            return sortedData;
        }

        private static void EnsureRecursiveRelation(DbRelation recursiveRelation)
        {
            bool isRecursive = (recursiveRelation.Parent.TableName == recursiveRelation.Child.TableName);
            if (!isRecursive)
                throw new ArgumentException(Messages.GenericHierarchicalQueryExecutor_RecursiveRelationIsRequired, "recursiveRelation");
        }

        private static void EnsureValidBeginAndEndLevels(int beginAtLevel, int endAtLevel)
        {
            if (beginAtLevel < 0) 
                throw new ArgumentException(Messages.GenericHierarchicalQueryExecutor_MustBePositiveInteger, "beginAtLevel");
            if (endAtLevel < 0) 
                throw new ArgumentException(Messages.GenericHierarchicalQueryExecutor_MustBePositiveInteger, "endAtLevel");
            if (endAtLevel < beginAtLevel)
                throw new ArgumentException(Messages.GenericHierarchicalQueryExecutor_EndLevelMustBeGreaterThanBeginLevel);
        }

        private static int GetEndLevel(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, int endAtLevel)
        {
            // Levels are zero-based indexes. Minimum depth for any tree is 1. Max level = depth - 1.
            int treeDepth = DetermineTreeDepthInternal(conn, rootEntity, recursiveRelation);
            int maxLevelInTree = treeDepth - 1;
            if (endAtLevel > maxLevelInTree)
                endAtLevel = maxLevelInTree;

            return endAtLevel;
        }

        #endregion

        #region SelectTree.

        /// <summary>Selects rows in the hierarchy under the current entity.</summary>
        /// <param name="conn">Connection-transcation context to use.</param>
        /// <param name="rootEntity">Root entity.</param>
        /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param>
        /// <param name="leafFilter">The search condition which restricts the rows returned. Often a criteria which filters out deactivated records.</param>
        /// <param name="sorter">Order by.</param>
        /// <param name="sortOnDb">Specifies whether sorting will take place on database or in DataTable after data has been fetched.</param>
        /// <param name="beginAtLevel">Zero-based index of the first level to fetch. Zero indicates that fetching starts at current entity,
        /// one indicates that fetching starts at the level directly beneath it etc.</param>
        /// <param name="endAtLevel">Zero-based index of the last level to fetch. Zero indicates that fetching ends at current entity,
        /// one indicates that fetching ends at the level directly beneath it etc. <see cref="System.Int32.MaxValue"/> indicates that operation ends at leaf nodes.</param>
        /// <returns>Rows in the hierarchy under the current entity.</returns>
        public DataTable SelectTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, OrderByClause sorter, bool sortOnDb, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);

            DataTable mergedData;
            if (SearchCondition.IsNullOrEmpty(leafFilter))
                mergedData = SelectAll(conn, rootEntity, recursiveRelation, beginAtLevel, endAtLevel);
            else
                mergedData = SelectWithLeafFilter(conn, rootEntity, recursiveRelation, leafFilter, beginAtLevel, endAtLevel);

            if (mergedData == null)
                mergedData = rootEntity.Table.NewDataTable();
            if (mergedData.Rows.Count > 0)
                mergedData = SortDataTable(mergedData, sorter);

            return mergedData;
        }

        private static DataTable SelectWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: final level reached (tree depth is measured).
            // It's possible that at some intermediate levels no records satisfy the specified criteria.
            // Nevertheless, the algorithm must proceed to the next level where it might find matching records.
            // This behavior simulates the behavior of recursive CTEs.
            DataTable mergedData = null;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.OpenConnection();

                endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel);
                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    SelectStatement fetchLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, leafFilter, level, LevelQuerySelectList.AllColumns);
                    DataTable levelData = fetchLevel.Execute(conn);
                    if (levelData.Rows.Count > 0)
                        AppendData(levelData, ref mergedData);

                    reachedEndLevel = (level >= endAtLevel);
                    level++;
                } while (!reachedEndLevel);
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            return mergedData;
        }

        private static DataTable SelectAll(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: select returns nothing.
            DataTable mergedData = null;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.OpenConnection();

                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    SelectStatement fetchLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.AllColumns);
                    DataTable levelData = fetchLevel.Execute(conn);
                    if (levelData.Rows.Count > 0)
                        AppendData(levelData, ref mergedData);

                    reachedEndLevel = (levelData.Rows.Count == 0) || (level >= endAtLevel);
                    level++;
                } while (!reachedEndLevel);
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            return mergedData;
        }

        #endregion

        #region DetermineTreeDepth.

        /// <summary>Counts the number of levels in the hieararchy starting with the current entity.</summary>
        /// <param name="conn">Connection-transcation context to use.</param>
        /// <param name="rootEntity">Root entity.</param>
        /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param>
        /// <returns>Sub-tree depth.</returns>
        public int DetermineTreeDepth(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation)
        {
            return DetermineTreeDepthInternal(conn, rootEntity, recursiveRelation);
        }

        private static int DetermineTreeDepthInternal(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation)
        {
            EnsureRecursiveRelation(recursiveRelation);
            int depth = 0;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.OpenConnection();

                bool reachedEndLevel = false;
                do
                {
                    SelectStatement countAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, depth, LevelQuerySelectList.Count);
                    DataTable results = countAtLevel.Execute(conn);
                    int nodeCount = Convert.ToInt32(results.Rows[0][0], CultureInfo.InvariantCulture);
                    if (nodeCount > 0)
                        depth++;
                    else
                        reachedEndLevel = true;
                } while (!reachedEndLevel);
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            return depth;
        }

        #endregion

        #region UpdateTree.

        /// <summary>Updates rows in the hierarchy under the current entity.</summary>
        /// <param name="conn">Connection-transcation context to use.</param>
        /// <param name="rootEntity">Root entity.</param>
        /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param>
        /// <param name="setExpressions">The list of columns to be updated and expressions that compute/contain the new values.</param>
        /// <param name="leafFilter">The search condition which limits the number of rows that are updated. Often a criteria which filters out deactivated records.</param>
        /// <param name="beginAtLevel">Zero-based index of the first level to update. Zero indicates that updating starts at current entity,
        /// one indicates that updating starts at the level directly beneath it etc.</param>
        /// <param name="endAtLevel">Zero-based index of the last level to update. Zero indicates that updating ends at current entity,
        /// one indicates that updating ends at the level directly beneath it etc. <see cref="System.Int32.MaxValue"/> indicates that operation ends at leaf nodes.</param>
        /// <returns>Number of rows affected.</returns>
        public int UpdateTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);
            int totalRowsAffected;
            if (SearchCondition.IsNullOrEmpty(leafFilter))
                totalRowsAffected = UpdateAll(conn, rootEntity, recursiveRelation, setExpressions, beginAtLevel, endAtLevel);
            else
                totalRowsAffected = UpdateWithLeafFilter(conn, rootEntity, recursiveRelation, setExpressions, leafFilter, beginAtLevel, endAtLevel);

            return totalRowsAffected;
        }

        private static int UpdateWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: final level reached (tree depth is measured).
            // It's possible that at some intermediate levels no records satisfy the specified criteria.
            // Nevertheless, the algorithm must proceed to the next level where it might find matching records.
            // This behavior simulates the behavior of recursive CTEs.
            int totalRowsAffected = 0;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.BeginTransaction();

                endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel);
                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    UpdateStatement updateAtLevel = CreateUpdateLevelStatement(rootEntity, recursiveRelation, setExpressions, leafFilter, level);
                    totalRowsAffected += updateAtLevel.Execute(conn);

                    reachedEndLevel = (level >= endAtLevel);
                    level++;
                } while (!reachedEndLevel);

                if (isLocalConn)
                    conn.CommitTransaction();
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            return totalRowsAffected;
        }

        // Specifies whether UpdateAll method will use rows affected number for stop conditions which provides significantly better performance.
        // Otherwise a SELECT COUNT statement must be issued each time to determine whether the method should proceed to the next level.
        enum RowsAffectedCredibility
        {
            NotDetermined,
            Trusted,
            Untrusted
        }

        private static int UpdateAll(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: rowsAffected == 0
            int totalRowsAffected = 0;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.BeginTransaction();

                // We need to perform COUNT at least once to determine whether rowsAffacted returned by UPDATE can be trusted.
                RowsAffectedCredibility trustRowsAffected = RowsAffectedCredibility.NotDetermined;
                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    // Dependening on DBMS configuration, update statements may automatically return the number of rows affected.
                    int rowsAffectedReturnedByEngine = UpdateAtLevel(conn, rootEntity, recursiveRelation, setExpressions, level);

                    // We need to perform manual counting for as long as rows affected cannot be trusted.
                    int? manualNodeCount = CountNodesAtLevelIfRowsAffectedCannotBeTrusted(conn, rootEntity, trustRowsAffected, recursiveRelation, level);                    
                    
                    // Evaluation which determines whether rows affected may be trusted takes place only once, ie. if not yet determined.
                    bool evaluateRowsAffectedCredibility = (trustRowsAffected == RowsAffectedCredibility.NotDetermined) && (manualNodeCount.HasValue);
                    if (evaluateRowsAffectedCredibility)
                    {
                        bool engineCountMatchesManualCount = (rowsAffectedReturnedByEngine == manualNodeCount.Value);
                        trustRowsAffected = (engineCountMatchesManualCount) ? RowsAffectedCredibility.Trusted : RowsAffectedCredibility.Untrusted;
                    }

                    // Manual node count is null if we have determined that rows affected value returned by engine is credible.
                    int rowsUpdatedInCurrentLevel = manualNodeCount ?? rowsAffectedReturnedByEngine;
                    totalRowsAffected += rowsUpdatedInCurrentLevel;
                    
                    // Inspect stop condition before level variable is increased.
                    reachedEndLevel = IsEndLevelReached(endAtLevel, level, rowsUpdatedInCurrentLevel);

                    // Next level.
                    level++;
                } while (!reachedEndLevel);

                if (isLocalConn)
                    conn.CommitTransaction();
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            return totalRowsAffected;
        }

        private static int? CountNodesAtLevelIfRowsAffectedCannotBeTrusted(IConnectionProvider conn, IEntity rootEntity, RowsAffectedCredibility trustRowsAffected, DbRelation recursiveRelation, int level)
        {
            int? manualNodeCount = null;
            if (trustRowsAffected != RowsAffectedCredibility.Trusted)
            {
                SelectStatement countAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.Count);
                DataTable countResults = countAtLevel.Execute(conn);
                manualNodeCount = Convert.ToInt32(countResults.Rows[0][0], CultureInfo.InvariantCulture);
            }

            return manualNodeCount;
        }

        private static bool IsEndLevelReached(int endAtLevel, int level, int rowsAffected)
        {
            return (rowsAffected == 0) || (level >= endAtLevel);
        }

        private static int UpdateAtLevel(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, int level)
        {
            UpdateStatement updateAtLevel = CreateUpdateLevelStatement(rootEntity, recursiveRelation, setExpressions, null, level);
            int rowsAffected = updateAtLevel.Execute(conn);
            return rowsAffected;
        }

        private static UpdateStatement CreateUpdateLevelStatement(IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, SearchCondition leafFilter, int level)
        {
            // Leaf filter, if provided, must be rendered in UPDATE-WHERE clause, not in SELECT-WHERE clause.
            // Target table of the subquery will an automatically genereted name - which is not used in leaf filter.
            IDbTable table = rootEntity.Table;
            UpdateStatement updateAtLevel = new UpdateStatement(table);
            updateAtLevel.UpdateList = setExpressions;
            SelectStatement queryLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.PrimaryKey);
            foreach (IDbColumn pkPart in table.PrimaryKey)
            {
                IDbColumn subQueryPkPart = queryLevel.FromTable.Columns.GetByColumnName(pkPart.ColumnName);
                queryLevel.Where.Add(PredicateFactory.Compare(pkPart, "=", subQueryPkPart));
            }
            updateAtLevel.Where.Add(PredicateFactory.Exists(queryLevel));
            if (leafFilter != null && !leafFilter.IsEmpty)
                updateAtLevel.Where.Add(leafFilter);

            return updateAtLevel;
        }

        #endregion

        #region CountTree.

        /// <summary>Counts rows in the hierarchy under the current entity.</summary>
        /// <param name="conn">Connection-transcation context to use.</param>
        /// <param name="rootEntity">Root entity.</param>
        /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param>
        /// <param name="leafFilter">The search condition which restricts the rows counted. Often a criteria which filters out deactivated records.</param>
        /// <param name="beginAtLevel">Zero-based index of the first level to process. Zero indicates that counting starts at current entity,
        /// one indicates that counting starts at the level directly beneath it etc.</param>
        /// <param name="endAtLevel">Zero-based index of the last level to process. Zero indicates that counting ends at current entity,
        /// one indicates that counting ends at the level directly beneath it etc. <see cref="System.Int32.MaxValue"/> indicates that operation ends at leaf nodes.</param>
        /// <returns>Total row count in the hierarchy.</returns>
        public int CountTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);
            int totalCount;
            if (SearchCondition.IsNullOrEmpty(leafFilter))
                totalCount = CountAll(conn, rootEntity, recursiveRelation, beginAtLevel, endAtLevel);
            else
                totalCount = CountWithLeafFilter(conn, rootEntity, recursiveRelation, leafFilter, beginAtLevel, endAtLevel);

            return totalCount;
        }

        private static int CountWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: final level reached (tree depth is measured).
            int totalCount = 0;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.OpenConnection();

                endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel);
                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    SelectStatement countAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, leafFilter, level, LevelQuerySelectList.Count);
                    DataTable results = countAtLevel.Execute(conn);
                    totalCount += Convert.ToInt32(results.Rows[0][0], CultureInfo.InvariantCulture);

                    reachedEndLevel = (level >= endAtLevel);
                    level++;
                } while (!reachedEndLevel);
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            return totalCount;
        }

        private static int CountAll(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: count returns zero.
            int totalCount = 0;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.OpenConnection();

                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    SelectStatement countAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.Count);
                    DataTable results = countAtLevel.Execute(conn);
                    int nodeCount = Convert.ToInt32(results.Rows[0][0], CultureInfo.InvariantCulture);

                    totalCount += nodeCount;
                    reachedEndLevel = (nodeCount == 0) || (level >= endAtLevel);
                    level++;
                } while (!reachedEndLevel);
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            return totalCount;
        }

        #endregion

        #region SumTree.

        /// <summary>Retrieves the sum of the column values in the hierarchy under the current entity.</summary>
        /// <param name="conn">Connection-transcation context to use.</param>
        /// <param name="rootEntity">Root entity.</param>
        /// <param name="column">Column. Must be a numeric column that belongs to the current entity's table.</param>
        /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param>
        /// <param name="leafFilter">The search condition which restricts the rows processed. Often a criteria which filters out deactivated records.</param>
        /// <param name="beginAtLevel">Zero-based index of the first level to process. Zero indicates that summing starts at current entity,
        /// one indicates that summing starts at the level directly beneath it etc.</param>
        /// <param name="endAtLevel">Zero-based index of the last level to process. Zero indicates that summing ends at current entity,
        /// one indicates that summing ends at the level directly beneath it etc. <see cref="System.Int32.MaxValue"/> indicates that operation ends at leaf nodes.</param>
        /// <returns>Total sum of column values in the hierarchy or <see cref="DBNull.Value"/>.</returns>
        public object SumTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);
            object totalSum;
            if (SearchCondition.IsNullOrEmpty(leafFilter))
                totalSum = SumAll(conn, rootEntity, column, recursiveRelation, beginAtLevel, endAtLevel);
            else
                totalSum = SumWithLeafFilter(conn, rootEntity, column, recursiveRelation, leafFilter, beginAtLevel, endAtLevel);

            return totalSum;
        }

        private static object SumWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: final level reached (tree depth is measured).
            object totalSum = null;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.OpenConnection();

                endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel);
                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    SelectStatement sumAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, leafFilter, level, LevelQuerySelectList.Default);
                    sumAtLevel.SelectList.Clear();
                    IDbColumn summedColumn = sumAtLevel.FromTable.Columns.GetByColumnName(column.ColumnName);
                    sumAtLevel.SelectList.Add(AggregateFunctionFactory.Sum(summedColumn, false, "levelSum"));
                    DataTable results = sumAtLevel.Execute(conn);
                    object levelSum = results.Rows[0]["levelSum"];

                    totalSum = SumNullableValues(column, totalSum, levelSum);
                    reachedEndLevel = (level >= endAtLevel);
                    level++;
                } while (!reachedEndLevel);
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            return totalSum;
        }

        private static object SumAll(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: count returns zero.
            object totalSum = null;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.OpenConnection();

                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    SelectStatement sumAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.Default);
                    sumAtLevel.SelectList.Clear();
                    IDbColumn summedColumn = sumAtLevel.FromTable.Columns.GetByColumnName(column.ColumnName);
                    sumAtLevel.SelectList.Add(AggregateFunctionFactory.Sum(summedColumn, false, "levelSum"));
                    sumAtLevel.SelectList.Add(AggregateFunctionFactory.Count("nodeCount"));
                    DataTable results = sumAtLevel.Execute(conn);
                    object levelSum = results.Rows[0]["levelSum"];
                    int nodeCount = Convert.ToInt32(results.Rows[0]["nodeCount"], CultureInfo.InvariantCulture);

                    totalSum = SumNullableValues(column, totalSum, levelSum);
                    reachedEndLevel = (nodeCount == 0) || (level >= endAtLevel);
                    level++;
                } while (!reachedEndLevel);
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            return totalSum;
        }

        private static object SumNullableValues(IDbColumn col, object x, object y)
        {
            object sum;
            if (x == null || x == DBNull.Value)
            {
                sum = y;
            }
            else if (y == null || y == DBNull.Value)
            {
                sum = x;
            }
            else
            {
                if (TypeUtil.IsInteger(col.DataType))
                {
                    // Try to return 32bit number. This is what most developers expect.
                    long bigsum = Convert.ToInt64(x, CultureInfo.InvariantCulture) + Convert.ToInt64(y, CultureInfo.InvariantCulture);
                    if (bigsum > int.MaxValue)
                        sum = bigsum;
                    else
                        sum = Convert.ToInt32(bigsum, CultureInfo.InvariantCulture);
                }
                else if (col.DataType == typeof(decimal))
                {
                    // Try not to loose precision.
                    decimal preciseSum = Convert.ToDecimal(x, CultureInfo.InvariantCulture) + Convert.ToDecimal(y, CultureInfo.InvariantCulture);
                    sum = preciseSum;
                }
                else if (TypeUtil.IsFloatingPointNumber(col.DataType))
                {
                    double bigsum = Convert.ToDouble(x, CultureInfo.InvariantCulture) + Convert.ToDouble(y, CultureInfo.InvariantCulture);
                    if (bigsum > float.MaxValue)
                        sum = bigsum;
                    else
                        sum = Convert.ToSingle(bigsum, CultureInfo.InvariantCulture);
                }
                else
                {
                    //throw new ArgumentException("Only numbers can be summed. Column '" + col.ColumnName + "' type is: " + col.DataType.Name, "col");
                    throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Messages.GenericHierarchicalQueryExecutor_OnyNumberMayBeSummedColXTypeIsY, col.ColumnName, col.DataType.Name), "col");
                }
            }

            return sum;
        }

        #endregion

        #region MinInTree.

        /// <summary>Retrieves the minimum column value in the hierarchy under the current entity.</summary>
        /// <param name="conn">Connection-transcation context to use.</param>
        /// <param name="rootEntity">Root entity.</param>
        /// <param name="column">Column to compare. Must belong to the current entity's table.</param>
        /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param>
        /// <param name="leafFilter">The search condition which restricts the rows processed. Often a criteria which filters out deactivated records.</param>
        /// <param name="beginAtLevel">Zero-based index of the first level to process. Zero indicates that comparison starts at current entity,
        /// one indicates that comparison starts at the level directly beneath it etc.</param>
        /// <param name="endAtLevel">Zero-based index of the last level to process. Zero indicates that comparison ends at current entity,
        /// one indicates that comparison ends at the level directly beneath it etc. <see cref="System.Int32.MaxValue"/> indicates that operation ends at leaf nodes.</param>
        /// <returns>Minimum value or <see cref="DBNull.Value"/>.</returns>
        public object MinInTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);
            object minimum;
            if (SearchCondition.IsNullOrEmpty(leafFilter))
                minimum = MinOfAll(conn, rootEntity, column, recursiveRelation, beginAtLevel, endAtLevel);
            else
                minimum = MinWithLeafFilter(conn, rootEntity, column, recursiveRelation, leafFilter, beginAtLevel, endAtLevel);

            return minimum;
        }

        private static object MinWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: final level reached (tree depth is measured).
            object minimum = null;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.OpenConnection();

                endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel);
                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    SelectStatement minAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, leafFilter, level, LevelQuerySelectList.Default);
                    minAtLevel.SelectList.Clear();
                    IDbColumn comparedColumn = minAtLevel.FromTable.Columns.GetByColumnName(column.ColumnName);
                    minAtLevel.SelectList.Add(AggregateFunctionFactory.Min(comparedColumn, "levelMin"));
                    DataTable results = minAtLevel.Execute(conn);
                    object levelMin = results.Rows[0]["levelMin"];

                    minimum = MinOfNullableValues(minimum as IComparable, levelMin as IComparable);
                    reachedEndLevel = (level >= endAtLevel);
                    level++;
                } while (!reachedEndLevel);
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            if (minimum == null)
                minimum = DBNull.Value;

            return minimum;
        }

        private static object MinOfAll(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: count returns zero.
            object minimum = null;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.OpenConnection();

                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    object levelMin;
                    int nodeCount;
                    FetchMinFromColumnAndCountAllNodesAtLevel(conn, rootEntity, recursiveRelation, level, column.ColumnName, out levelMin, out nodeCount);

                    minimum = MinOfNullableValues(minimum as IComparable, levelMin as IComparable);
                    reachedEndLevel = (nodeCount == 0) || (level >= endAtLevel);
                    level++;
                } while (!reachedEndLevel);
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            if (minimum == null)
                minimum = DBNull.Value;

            return minimum;
        }

        private static void FetchMinFromColumnAndCountAllNodesAtLevel(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, int level, string minFromColumnName, out object levelMin, out int nodeCount)
        {
            SelectStatement minAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.Default);
            minAtLevel.SelectList.Clear();
            IDbColumn comparedColumn = minAtLevel.FromTable.Columns.GetByColumnName(minFromColumnName);
            minAtLevel.SelectList.Add(AggregateFunctionFactory.Min(comparedColumn, "levelMin"));
            minAtLevel.SelectList.Add(AggregateFunctionFactory.Count("nodeCount"));
            DataTable results = minAtLevel.Execute(conn);
            levelMin = results.Rows[0]["levelMin"];
            nodeCount = Convert.ToInt32(results.Rows[0]["nodeCount"], CultureInfo.InvariantCulture);
        }

        private static object MinOfNullableValues(IComparable x, IComparable y)
        {
            // DBNull.Value have been converted to null when "first as IComparable" and "second as IComparable" are evaluated.
            object lesserValue;
            if (x == null)
            {
                lesserValue = y;
            }
            else if (y == null)
            {
                lesserValue = x;
            }
            else
            {
                bool xLessThanY = (x.CompareTo(y) < 0);
                lesserValue = (xLessThanY) ? x : y;
            }

            return lesserValue;
        }

        #endregion

        #region MaxInTree.

        /// <summary>Retrieves the maximum column value in the hierarchy under the current entity.</summary>
        /// <param name="conn">Connection-transcation context to use.</param>
        /// <param name="rootEntity">Root entity.</param>
        /// <param name="column">Column to compare. Must belong to the current entity's table.</param>
        /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param>
        /// <param name="leafFilter">The search condition which restricts the rows processed. Often a criteria which filters out deactivated records.</param>
        /// <param name="beginAtLevel">Zero-based index of the first level to process. Zero indicates that comparison starts at current entity,
        /// one indicates that comparison starts at the level directly beneath it etc.</param>
        /// <param name="endAtLevel">Zero-based index of the last level to process. Zero indicates that comparison ends at current entity,
        /// one indicates that comparison ends at the level directly beneath it etc. <see cref="System.Int32.MaxValue"/> indicates that operation ends at leaf nodes.</param>
        /// <returns>Maximum value or <see cref="DBNull.Value"/>.</returns>
        public object MaxInTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);
            object maximum;
            if (SearchCondition.IsNullOrEmpty(leafFilter))
                maximum = MaxOfAll(conn, rootEntity, column, recursiveRelation, beginAtLevel, endAtLevel);
            else
                maximum = MaxWithLeafFilter(conn, rootEntity, column, recursiveRelation, leafFilter, beginAtLevel, endAtLevel);

            return maximum;
        }

        private static object MaxWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: final level reached (tree depth is measured).
            object maximum = null;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.OpenConnection();

                endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel);
                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    SelectStatement maxAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, leafFilter, level, LevelQuerySelectList.Default);
                    maxAtLevel.SelectList.Clear();
                    IDbColumn comparedColumn = maxAtLevel.FromTable.Columns.GetByColumnName(column.ColumnName);
                    maxAtLevel.SelectList.Add(AggregateFunctionFactory.Max(comparedColumn, "levelMax"));
                    DataTable results = maxAtLevel.Execute(conn);
                    object levelMax = results.Rows[0]["levelMax"];

                    maximum = MaxOfNullableValues(maximum as IComparable, levelMax as IComparable);
                    reachedEndLevel = (level >= endAtLevel);
                    level++;
                } while (!reachedEndLevel);
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            if (maximum == null)
                maximum = DBNull.Value;

            return maximum;
        }

        private static object MaxOfAll(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, int beginAtLevel, int endAtLevel)
        {
            // Stop condition: count returns zero.
            object maximum = null;
            bool isLocalConn = !conn.IsOpen;
            try
            {
                if (isLocalConn)
                    conn.OpenConnection();

                bool reachedEndLevel = false;
                int level = beginAtLevel;
                do
                {
                    object levelMax;
                    int nodeCount;
                    FetchMaxFromColumnAndCountAllNodesAtLevel(conn, rootEntity, recursiveRelation, level, column.ColumnName, out levelMax, out nodeCount);

                    maximum = MaxOfNullableValues(maximum as IComparable, levelMax as IComparable);
                    reachedEndLevel = (nodeCount == 0) || (level >= endAtLevel);
                    level++;
                } while (!reachedEndLevel);
            }
            finally
            {
                if (isLocalConn)
                    conn.CloseConnection();
            }

            if (maximum == null)
                maximum = DBNull.Value;

            return maximum;
        }

        private static void FetchMaxFromColumnAndCountAllNodesAtLevel(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, int level, string maxFromColumnName, out object levelMax, out int nodeCount)
        {
            SelectStatement maxAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.Default);
            maxAtLevel.SelectList.Clear();
            IDbColumn comparedColumn = maxAtLevel.FromTable.Columns.GetByColumnName(maxFromColumnName);
            maxAtLevel.SelectList.Add(AggregateFunctionFactory.Max(comparedColumn, "levelMax"));
            maxAtLevel.SelectList.Add(AggregateFunctionFactory.Count("nodeCount"));
            
            DataTable results = maxAtLevel.Execute(conn);
            levelMax = results.Rows[0]["levelMax"];
            nodeCount = Convert.ToInt32(results.Rows[0]["nodeCount"], CultureInfo.InvariantCulture);
        }

        private static object MaxOfNullableValues(IComparable x, IComparable y)
        {
            // DBNull.Value have been converted to null when "first as IComparable" and "second as IComparable" are evaluated.
            object greaterValue;
            if (x == null)
            {
                greaterValue = y;
            }
            else if (y == null)
            {
                greaterValue = x;
            }
            else
            {
                bool xGreaterThanY = (x.CompareTo(y) > 0);
                greaterValue = (xGreaterThanY) ? x : y;
            }

            return greaterValue;
        }

        #endregion
    }
}